-- @author lixiyong01
-- @date 2023.03.16
-- 健康分详情表-任务

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score partition (dt=${dateSuffix}, tag='task', code='task_depend')
select
    a.hadoop_queue_name as project,
    b.org_cn_name as team,
    nvl(a.user_oa,'-') as owner_id,
    a.task_id as app_id,
    a.task_name as app_name,
    case
        -- 表已经做了人工审核过，也直接100分
        -- when c.is_managed then '100,人工审核过'

        -- 上游依赖级别异常，扣10分
        when a.relation_task_level<>1 then '090,上游依赖级别异常'

        else '100,依赖完整'
    end as health_score,
    concat_ws(',',
        'relation_task_id', cast(nvl(a.relation_task_id,0) as string),
        'relation_task_name',nvl(a.relation_task_name,'-'),
        'relation_task_level', cast(nvl(a.relation_task_level,0) as string),
        'relation_task_user_oa',nvl(a.relation_task_user_oa,'-')
    ) as data_content,
    (10-1)*1000 as weight
from (
    select task_id, task_name, user_oa,
        relation_task_id, relation_task_name, relation_task_level, relation_task_user_oa,
        hadoop_queue_name, hadoop_account
    from hdp_lbg_supin_zplisting.app_zp_rock_task_relation_monitor
    where dt='${#date(0,0,-1):yyyyMMdd#}'
) a
left join (
    select
        username,
        realname as user_name_cn,
        concat(
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-4],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-3],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-2],'|',
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-1]
        ) as org_cn_name
    from hdp_lbg_supin_zplisting.dim_zp_n_n_emp_org
    where dt = '${#date(0, 0, -1):yyyyMMdd#}'
) b on a.user_oa = b.username;


